import dash
from dash import dcc, html
import plotly.graph_objects as go
from models import db, Organization, Product,StatisticsData
from sqlalchemy import func, cast, Float, desc # 导入 desc
from server import server
from datetime import date

# 1. 获取全体设备的统计信息
def get_products_summary():
    
    with server.app_context():
        # 查找最近的统计日期
        latest_date_row = db.session.query(func.max(StatisticsData.stat_date)).scalar()
        if not latest_date_row:
            return {
                'equipment_count': 0,
                'total_amount': 0,
                'total_health_rate': 0,
                'avg_age_index': 0
            }
        latest_date = latest_date_row

        stats = db.session.query(StatisticsData).filter(
            StatisticsData.stat_date == latest_date # 使用最近日期过滤
        ).all()
        equipment_count = sum([s.equipment_count or 0 for s in stats])
        total_amount = sum([s.total_value or 0 for s in stats])
        total_health_rate = (
            sum([(s.health_rate or 0) * (s.equipment_count or 0) for s in stats]) / equipment_count
            if equipment_count else 0
        )
        avg_age_index = (
            sum([(s.age_index or 0) * (s.equipment_count or 0) for s in stats]) / equipment_count
            if equipment_count else 0
        )
        # print(f"p26 [get_products_summary] today={today}, equipment_count={equipment_count}, total_amount={total_amount}, total_health_rate={total_health_rate}, avg_age_index={avg_age_index}")
        return {
            'equipment_count': equipment_count,
            'total_amount': total_amount,
            'total_health_rate': total_health_rate,
            'avg_age_index': avg_age_index
        }

# 2. 生成分机构设备数量和金额图
def create_org_equipment_amount_chart(org_names, equipment_counts, total_amounts):
    """
    生成分机构设备数量和金额的柱状+折线图
    """
    # 替换为从StatisticsData获取数据
   
    with server.app_context():
        # 查找最近的统计日期
        latest_date_row = db.session.query(func.max(StatisticsData.stat_date)).scalar()
        if not latest_date_row:
            return go.Figure() # 如果没有数据，返回一个空图表
        latest_date = latest_date_row

        stats = db.session.query(StatisticsData).filter(
            StatisticsData.stat_date == latest_date # 使用最近日期过滤
        ).all()
        org_names = [s.org_shortname_c for s in stats]
        equipment_counts = [s.equipment_count or 0 for s in stats]
        total_amounts = [s.total_value or 0 for s in stats]
        # 按设备数量降序排列
        combined = list(zip(org_names, equipment_counts, total_amounts))
        combined.sort(key=lambda x: x[1], reverse=True)
        org_names, equipment_counts, total_amounts = zip(*combined) if combined else ([], [], [])
    bar = go.Bar(
        x=org_names,
        y=equipment_counts,
        width=0.4,  # 这里设置柱子的宽度，数值越小柱子越细
        name='设备数量',
        marker_color='#3399ff',

        text=equipment_counts,
        textposition='auto',
        textfont={'color': 'white', 'size': 10},
        yaxis='y'
    )
    line = go.Scatter(
        x=org_names,
        y=total_amounts,
        name='资产金额',
        mode='lines+markers',
        line={'color': '#FFFFFF', 'width': 1},
        marker={'size': 6, 'color': '#FF9F40'},
        yaxis='y2'
    )
    fig = go.Figure([bar, line])
    fig.update_layout(
        title={
            'text': f"设备总数:{get_products_summary()['equipment_count']}台  资产总额:￥{get_products_summary()['total_amount']}万元",
            'font': {'size': 14, 'color': 'white'},
            'x': 0.5,
            'y': 0.98,
            'xanchor': 'center',
            'yanchor': 'top'
        },
        plot_bgcolor="#112360",
        paper_bgcolor="#112360",
        font=dict(color='white'),
        margin=dict(l=0, r=0, t=30, b=50),
        legend=dict(
            orientation="h",
            yanchor="bottom",
            y=0.9,
            xanchor="center",
            x=0.5,
            bgcolor='rgba(0,0,0,0)'
        ),
        xaxis=dict(
            showgrid=False,
            tickangle=45,
            tickfont={'size': 10},
            showline=False,
            zeroline=False
        ),
        yaxis=dict(
            title='设备数量',
            showgrid=False,
            showticklabels=True,
            zeroline=False,
            automargin=True
        ),
        yaxis2=dict(
            title='资产金额(万元)',
            showgrid=False,
            showticklabels=True,
            side='right',
            overlaying='y',
            zeroline=False,
            automargin=True
        ),
        bargap=0.15,
        bargroupgap=0.1
    )
    return fig

# 3. 获取分机构设备完好率
def get_org_running_status_list():
    """
    返回每个机构的设备完好率，格式为 [{'机构名': 完好率}, ...]
    完好率 = is_active=1 且 running_status=0 的数量 / is_active=1 的数量
    """
    result = []
    with server.app_context():
        orgs = db.session.query(Organization.short_name_c).all()
        org_names = [org[0] for org in orgs] if orgs else []
        for org_name in org_names:
            total = db.session.query(Product.id).filter(
                Product.org_name.contains(org_name),
                Product.is_active == 1
            ).count()
            healthy = db.session.query(Product.id).filter(
                Product.org_name.contains(org_name),
                Product.is_active == 1,
                Product.running_status == 0
            ).count()
            rate = round(healthy / total, 2) if total > 0 else 0.0
            result.append({org_name: rate})
    # print(f"bigscreen.charts.[get_org_running_status_list] result={result}")        
    return result

# 4. 生成分机构设备完好率和年限指数图
def create_org_equipment_health_chart(running_status_list):
    """
    生成分机构设备完好率和年限指数的柱状+折线图
    """
    
    with server.app_context():
        # 查找最近的统计日期
        latest_date_row = db.session.query(func.max(StatisticsData.stat_date)).scalar()
        if not latest_date_row:
             # 如果没有数据，返回一个空图表
            fig = go.Figure()
            summary = {'total_health_rate': 0, 'avg_age_index': 0}
            fig.update_layout(
                 title={
                    'text': f"总完好率:{summary['total_health_rate']*100:.2f}%  总年限指数:{round(summary['avg_age_index'],2)}",
                    'font': {'size': 14, 'color': 'white'},
                    'x': 0.5,
                    'y': 1.00,
                    'xanchor': 'center',
                    'yanchor': 'top'
                },
                plot_bgcolor="#112360",
                paper_bgcolor="#112360",
                font=dict(color='white'),
                margin=dict(l=10, r=10, t=30, b=50),
            )
            return fig

        latest_date = latest_date_row

        stats = db.session.query(StatisticsData).filter(
            StatisticsData.stat_date == latest_date # 使用最近日期过滤
        ).all()
        org_names = [s.org_shortname_c for s in stats]
        health_rates = [round((s.health_rate or 0) * 100, 2) for s in stats]
        avg_age_index_list = [round(s.age_index or 0, 2) for s in stats]
        # 按完好率降序排列
        combined = list(zip(org_names, health_rates, avg_age_index_list))
        combined.sort(key=lambda x: x[1], reverse=True)
        org_names, health_rates, avg_age_index_list = zip(*combined) if combined else ([], [], [])
    fig = go.Figure()
    fig.add_trace(go.Bar(
        x=org_names,
        y=health_rates,
        width=0.4,  # 这里设置柱子的宽度，数值越小柱子越细

        marker_color='#3399ff',
        name='设备完好率',
        text=[f'{r:.1f}%' for r in health_rates],
        textposition='auto',
        textfont={'color': 'white', 'size': 10},
        yaxis='y'
    ))
    fig.add_trace(go.Scatter(
        x=org_names,
        y=avg_age_index_list,
        name='年限指数',
        mode='lines+markers',
        line={'color': '#FFFFFF', 'width': 1},
        marker={'size': 6, 'color': '#FF9F40'},
        yaxis='y2'
    ))
    summary = get_products_summary()
    fig.update_layout(
        title={
            'text': f"总完好率:{summary['total_health_rate']*100:.2f}%  总年限指数:{round(summary['avg_age_index'],2)}",
            'font': {'size': 14, 'color': 'white'},

            'x': 0.5,
            'y': 0.98,
            'xanchor': 'center',
            'yanchor': 'top'
        },
        plot_bgcolor="#112360",
        paper_bgcolor="#112360",
        font=dict(color='white'),
        margin=dict(l=10, r=10, t=30, b=50),
        xaxis=dict(showgrid=False, tickangle=45, tickfont={'size': 10}),
        yaxis=dict(title='完好率(%)', range=[0, 120], showgrid=True, gridcolor='#333'),
        yaxis2=dict(
            title='年限指数',
            overlaying='y',
            side='right',
            showgrid=False,
            showticklabels=True,
            automargin=True
        ),
        showlegend=True,
        legend=dict(
            orientation='h',
            yanchor='bottom',
            y=0.9,
            xanchor='center',
            x=0.5,
            bgcolor='rgba(0,0,0,0)'
        )
    )
    return fig

# 获取所有机构名称
def get_all_org_names():
    with server.app_context():
        orgs = db.session.query(Organization.short_name_c).all()
        return [org[0] for org in orgs] if orgs else []

# 获取设备分类统计
def get_device_type_count(org_name='all'):
    with server.app_context():
        # 统计每个分类的总金额
        query = db.session.query(
            Product.equipment_category,
            db.func.sum(Product.price)
        )
        if org_name != 'all':
            query = query.filter(Product.org_name.contains(org_name))
        query = query.group_by(Product.equipment_category)
        result = query.all()
        # 按金额降序排序
        result = sorted(result, key=lambda x: x[1] or 0, reverse=True)
        top5 = result[:5]
        others = result[5:]
        if others:
            other_sum = sum([x[1] or 0 for x in others])
            top5.append(('其他', other_sum))
        labels = [r[0] or '未知' for r in top5]
        values = [r[1] or 0 for r in top5]
        # print(f"[get_device_type_count] labels={labels}, values={values}")
        return labels, values

def get_device_type_count_by_count(org_name='all'):
    with server.app_context():
        query = db.session.query(
            Product.equipment_category,
            db.func.count(Product.id)
        )
        if org_name != 'all':
            query = query.filter(Product.org_name.contains(org_name))
        query = query.group_by(Product.equipment_category)
        result = query.all()
        result = sorted(result, key=lambda x: x[1] or 0, reverse=True)
        top5 = result[:5]
        others = result[5:]
        if others:
            other_sum = sum([x[1] or 0 for x in others])
            top5.append(('其他', other_sum))
        labels = [r[0] or '未知' for r in top5]
        values = [r[1] or 0 for r in top5]
        return labels, values

def get_device_price_range_count(org_name='all'):
    # 价格区间直接用万元单位
    bins = [0, 1, 5, 10, 50, 100, 999999999]
    bin_labels = ['1万以下', '1-5万', '5-10万', '10-50万', '50-100万', '100万以上']
    with server.app_context():
        query = db.session.query(Product.price)
        if org_name != 'all':
            query = query.filter(Product.org_name.contains(org_name))
        prices = [p[0] or 0 for p in query.all()]
        # print(f"p280 [get_device_price_range_count] org_name={org_name}, prices={prices}")
        amounts = [0] * len(bin_labels)
        for price in prices:
            for i in range(len(bins)-1):
                if bins[i] <= price < bins[i+1]:
                    amounts[i] += price  # 累加金额
                    break
        # print(f"p287 [get_device_price_range_count] amounts={amounts}")
        return bin_labels, amounts

def create_device_type_donut(org_name='all', mode='amount'):
    # print(f"charts p335 create_device_type_donut org_name={org_name}, mode={mode}")
    if mode == 'amount':
        labels, values = get_device_type_count(org_name)
        hover = '金额: ￥%{value:.2f} 万'
    else:
        labels, values = get_device_type_count_by_count(org_name)
        hover = '台数: %{value} 台'
    total = sum(values)
    percent = [v / total for v in values]
    if "其他" in labels:
        idx = labels.index("其他")
        other_label = labels.pop(idx)
        other_value = values.pop(idx)
        other_percent = percent.pop(idx)
        zipped = list(zip(labels, values, percent))
        zipped.sort(key=lambda x: x[2], reverse=True)
        labels, values, percent = zip(*zipped) if zipped else ([], [], [])
        labels = list(labels) + [other_label]
        values = list(values) + [other_value]
    else:
        zipped = list(zip(labels, values, percent))
        zipped.sort(key=lambda x: x[2], reverse=True)
        labels, values, percent = zip(*zipped) if zipped else ([], [], [])
    fig = go.Figure(go.Pie(
        labels=labels,
        values=values,
        hole=0.618,
        sort=False,
        direction='clockwise',
        textinfo='none',
        insidetextorientation='radial',
        marker=dict(line=dict(width=2, color='white')),
        hovertemplate='%{label}<br>' + hover + '<extra></extra>'
    ))
    fig.update_traces(
        textfont_size=10,
        marker=dict(line=dict(width=2, color='white')),
        pull=[0.01]*len(labels)
    )
    fig.update_layout(
        title_text='',
        showlegend=False,
        plot_bgcolor="#112360",
        paper_bgcolor="#112360",
        font=dict(color='white'),
        margin=dict(l=10, r=10, t=0, b=50),
        annotations=[
            dict(
                text='分类金额分布' if mode == 'amount' else '分类台数分布',
                x=0.5, y=0.5, font_size=13, showarrow=False, font_color='white', align='center'
            )
        ]
    )
    return fig

def create_device_price_donut(org_name='all', mode='amount'):
    if mode == 'amount':
        labels, values = get_device_price_range_count(org_name)
        hover = '金额: ￥%{value:.2f} 万'
        annotation = '价格区间分布'
     
    else:
        # 统计台数
        bins = [0, 1, 5, 10, 50, 100, 999999999]
        bin_labels = ['1万以下', '1-5万', '5-10万', '10-50万', '50-100万', '100万以上']
        with server.app_context():
            query = db.session.query(Product.price)
            if org_name != 'all':
                query = query.filter(Product.org_name.contains(org_name))
            prices = [p[0] or 0 for p in query.all()]
            counts = [0] * len(bin_labels)
            for price in prices:
                for i in range(len(bins)-1):
                    if bins[i] <= price < bins[i+1]:
                        counts[i] += 1
                        break
        labels, values = bin_labels, counts
        hover = '台数: %{value} 台'
        annotation = '价位台数分布'
    total = sum(values)
    percent = [v / total for v in values] if total else [0]*len(values)
    zipped = list(zip(labels, values, percent))
    zipped.sort(key=lambda x: x[2], reverse=True)
    labels, values, percent = zip(*zipped) if zipped else ([], [], [])
    fig = go.Figure(go.Pie(
        labels=labels,
        values=values,
        hole=0.618,
        sort=False,
        direction='clockwise',
        textinfo='none',
        insidetextorientation='radial',
        marker=dict(line=dict(width=2, color='white')),
        hovertemplate='%{label}<br>' + hover + '<extra></extra>'
    ))
    fig.update_traces(
        textfont_size=12,
        marker=dict(line=dict(width=2, color='white')),
        pull=[0.01]*len(labels)
    )
    fig.update_layout(
        title_text='',
        showlegend=False,
        plot_bgcolor="#112360",
        paper_bgcolor="#112360",
        font=dict(color='white'),
        margin=dict(l=10, r=10, t=0, b=50),
        annotations=[
            dict(
                text=annotation,
                x=0.5, y=0.5, font_size=13, showarrow=False, font_color='white', align='center'
            )
        ]
    )
    return fig


